package cn.edu.wit.dao;

import cn.edu.wit.beans.Clazz;
import cn.edu.wit.beans.Student;
import cn.edu.wit.utils.DBManager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentDAO {
	
	public boolean insertStudent(Student s){
		boolean flag = false;
		try{
			Connection conn = DBManager.getConn();
			
			String sql = "insert into tb_stus(snum,name,sex,age,tel,qq,addr,remark,class_id) values(?,?,?,?,?,?,?,?,?)";
			PreparedStatement ps =  conn.prepareStatement(sql);
			
			ps.setString(1, s.getStuNum());
			ps.setString(2, s.getStuName());
			ps.setString(3, s.getStuSex());
			ps.setInt(4, s.getStuAge());
			ps.setString(5, s.getStuTel());
			ps.setString(6, s.getStuQQ());
			ps.setString(7, s.getStuAddr());
			ps.setString(8, s.getStuRemark());
			ps.setInt(9, s.getCla().getCid());
			
			int i= ps.executeUpdate();
			flag = i>0?true:false;
			conn.close();
			
			//�޸İ༶����
			flag = new ClazzDAO().changeCount(s.getCla().getCid(), 1);
			
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}

	public boolean deleteStudent(String stuNum){
		boolean flag = false;
		try {
			Student s = queryStudent(stuNum);
			
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_sims","root","admin123");
			
			String sql = "delete from tb_stus where snum=?";
			
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, stuNum);
			
			int i = ps.executeUpdate();
			flag = i>0?true:false;
			conn.close();
			//�޸İ༶����
			flag = new ClazzDAO().changeCount(s.getCla().getCid(), -1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	public boolean updateStudent(Student s){
		boolean flag = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_sims","root","admin123");
			
			String sql = "update tb_stus set name=?,sex=?,age=?,tel=?,qq=?,addr=?,remark=?,class_id=? where snum=?";
			
			PreparedStatement ps =  conn.prepareStatement(sql);
			ps.setString(1, s.getStuName());
			ps.setString(2, s.getStuSex());
			ps.setInt(3, s.getStuAge());
			ps.setString(4, s.getStuTel());
			ps.setString(5, s.getStuQQ());
			ps.setString(6, s.getStuAddr());
			ps.setString(7, s.getStuRemark());
			ps.setInt(8, s.getCla().getCid());
			ps.setString(9, s.getStuNum());
			
			int i= ps.executeUpdate();
			flag = i>0?true:false;
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}

	public Student queryStudent(String stuNum) {
		Student s = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_sims","root","admin123");
			
			String sql = "select * from tb_stus where snum=?";
			
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, stuNum);
			
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()){
				String stuName = rs.getString("name");
				String stuSex = rs.getString("sex");
				int stuAge = rs.getInt("age");
				String stuTel = rs.getString("tel");
				String stuQQ = rs.getString("qq");
				String stuAddr = rs.getString("addr");
				String stuRemark = rs.getString("remark");
				int cid = rs.getInt("class_id");
				Clazz cla = new ClazzDAO().queryClass(cid);
				
				s = new Student(stuNum, stuName, stuSex, stuAge, stuTel, stuQQ, stuAddr, stuRemark,cla);
			}
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return s;
	}

	public  List<Student> listStudents() {
		List<Student> stus = new ArrayList<Student>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_sims","root","admin123");
			String sql = "select * from tb_stus";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				String stuNum = rs.getString("snum");
				String stuName = rs.getString("name");
				String stuSex = rs.getString("sex");
				int stuAge = rs.getInt("age");
				String stuTel = rs.getString("tel");
				String stuQQ = rs.getString("qq");
				String stuAddr = rs.getString("addr");
				String stuRemark = rs.getString("remark");
				int cid = rs.getInt("class_id");
				Clazz cla = new ClazzDAO().queryClass(cid);
				
				Student s = new Student(stuNum, stuName, stuSex, stuAge, stuTel, stuQQ, stuAddr, stuRemark,cla);
				stus.add(s);
			}
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return stus;
	}
}
